package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import javax.swing.JOptionPane;

import javab.Actable;
import javab.Infotable;



public class DBUtil {
	Connection con;
	PreparedStatement ps;
	ResultSet rs;
	SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
	//数据库连接
	public Connection getDBConnection(String type) {
		con=DBFactory.createConnection(type);
		return con;
	}
	
	//查询日志文件信息
	public ArrayList<Infotable> query(){
		ArrayList<Infotable> arlist=new ArrayList<Infotable>();
		try {
			ps=con.prepareStatement("select * from infotable order by timest desc");
			rs=ps.executeQuery();
			while(rs.next()) {
				Infotable lg=new Infotable();
				lg.setTimest(rs.getString(1));
				lg.setOpertype(rs.getString(2));
				lg.setMoney(rs.getInt(3));
				arlist.add(lg);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "失败");
		}
		return arlist;
		
	}
	
	//更新数据存款
	public void updateAdd(int num) {
		con=DBFactory.createConnection("mysql");
		try {
			PreparedStatement psql=con.prepareStatement("update actable set balance=balance+?");
			psql.setInt(1, num);
			psql.executeUpdate();
			
			PreparedStatement psql1=con.prepareStatement("insert into infotable values(?,?,?)");
			psql1.setString(1, df.format(new Date()));
			psql1.setString(2, "存款");
			psql1.setInt(3, num);
			
			psql1.executeUpdate();
			JOptionPane.showMessageDialog(null, "存钱成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "存钱失败");
		}
	}
	
	//更新数据取款
	public void updateLess(int num) {
		con=DBFactory.createConnection("mysql");
		try {
			PreparedStatement psql=con.prepareStatement("update actable set balance=balance-?");
			psql.setInt(1, num);
			psql.executeUpdate();
			
			PreparedStatement psql1=con.prepareStatement("insert into infotable values(?,?,?)");
			psql1.setString(1, df.format(new Date()));
			psql1.setString(2, "取款");
			psql1.setInt(3, num);
			
			psql1.executeUpdate();
			JOptionPane.showMessageDialog(null, "取钱成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "取钱失败");
		}
	}
	
	//查询操作时间
	public ArrayList<Infotable> queryByTime(){
		ArrayList<Infotable> arlist=new ArrayList<Infotable>();
		try {
			ps=con.prepareStatement("select * from infotable order by timest desc");
			rs=ps.executeQuery();
			while(rs.next()) {
				Infotable lg=new Infotable();
				lg.setTimest(rs.getString(1));
				lg.setOpertype(rs.getString(2));
				lg.setMoney(rs.getInt(3));
				arlist.add(lg);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "操作时间搜索失败");
		}
		return arlist;
		
	}
	
	
	//交易类型查询
	public ArrayList<Infotable> queryByOperatype(){
		ArrayList<Infotable> arlist=new ArrayList<Infotable>();
		try {
			ps=con.prepareStatement("select * from infotable where opertype='存款'   order by money desc");
			rs=ps.executeQuery();
			while(rs.next()) {
				Infotable lg=new Infotable();
				lg.setTimest(rs.getString(1));
				lg.setOpertype(rs.getString(2));
				lg.setMoney(rs.getInt(3));
				arlist.add(lg);
			}
			
			ps=con.prepareStatement("select * from infotable where opertype='取款'   order by money desc");
			rs=ps.executeQuery();
			while(rs.next()) {
				Infotable lg=new Infotable();
				lg.setTimest(rs.getString(1));
				lg.setOpertype(rs.getString(2));
				lg.setMoney(rs.getInt(3));
				arlist.add(lg);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "操作时间搜索失败");
		}
		return arlist;
		
	}
	
	//用户信息
	public ArrayList<Actable> queryById(){
		ArrayList<Actable> arlist=new ArrayList<Actable>();
		try {
			ps=con.prepareStatement("select * from actable");
			rs=ps.executeQuery();
			while(rs.next()) {
				Actable a=new Actable();
				a.setAcid(rs.getString(1));
				a.setAcname(rs.getString(2));
				a.setAge(rs.getInt(3));
				a.setAddress(rs.getString(4));
				a.setBalance(rs.getInt(5));
				arlist.add(a);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "数据库操作错误");
		}
		return arlist;
	}
	
	//关闭数据库
	public void closeAll() {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(ps!=null) {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(con!=null) {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
//				e.printStackTrace();
				JOptionPane.showMessageDialog(null, "断开数据库错误");
			}
		}
	}
	
		
	
	
}
